package com.apestech.framework.sql.lib;

import com.apestech.framework.json.JSON;
import com.apestech.framework.sql.SqlUtil;
import java.util.ArrayList;

/**
 * @author hup
 * @create 2019-01-08-17:19
 */
public class SelSql extends SqlUtil {
    public class Sql{
        private Integer select;
        private Integer from;
        private Integer where;
        private Integer group;
        private Integer order;
        private Integer end;
        private boolean isEnd=false;
        private String lastkey;
        private Integer lastPos;

        public Integer getSelect() {
            return select;
        }

        public void setSelect(Integer select) {
            this.select = select;
            this.lastkey="SELECT";
            this.lastPos= select;
        }

        public Integer getFrom() {
            return from;
        }

        public void setFrom(Integer from) {
            this.from = from;
            this.lastkey="FROM";
            this.lastPos= from;
        }

        public Integer getWhere() {
            return where;
        }

        public void setWhere(Integer where) {
            this.where = where;
            this.lastkey="WHERE";
            this.lastPos= where;
        }

        public Integer getGroup() {
            return group;
        }

        public void setGroup(Integer group) {
            this.group = group;
            this.lastkey="GROUP";
            this.lastPos= group;
        }

        public Integer getOrder() {
            return order;
        }

        public void setOrder(Integer order) {
            this.order = order;
            this.lastkey="ORDER";
            this.lastPos= order;
        }

        public Integer getEnd() {
            return end;
        }

        public void setEnd(Integer end) {
            this.end = end;
        }

        public boolean isEnd() {
            return isEnd;
        }

        public void setEnd(boolean end) {
            isEnd = end;
        }

        public String getLastkey() {
            return lastkey;
        }

        public Integer getLastPos() {
            return lastPos;
        }
    }

    public class Key{
        private String key;
        private Integer pos;
        public Key(String key, Integer pos){
            this.key=key;
            this.pos=pos;
        }

        public String getKey() {
            return key;
        }

        public Integer getPos() {
            return pos;
        }
    }

    private ArrayList<Sql> sqllist=new ArrayList<Sql>();
    private ArrayList<Key> key=new ArrayList<Key>();
    private ArrayList<From> fromlist=new ArrayList<From>();
    private String sql;
    private String oldSql;
    private String fromStr;

    public SelSql(String sql){
        this.sql=sql.toUpperCase();
        this.oldSql=sql;
    }

    public void expain() throws Exception {
        key.clear();
        sqllist.clear();
        getList("SELECT");
        getList("FROM");
        getList("WHERE");
        getList("ORDER");
        getList("GROUP");
        getAllList();
        getFromList();
    }

    public String getFrom(){
        if(sqllist.size()==0) return null;
        if(fromlist.size()==0) {
            if(fromStr==null){
                Integer en=sqllist.get(0).getWhere();
                if(en==null) en=sqllist.get(0).getEnd();
                fromStr=oldSql.substring(sqllist.get(0).getFrom()+4, en).trim();
            }
            return fromStr;
        }
        return null;
    }

    public ArrayList<From> getFromlist() {
        return fromlist;
    }

    public ArrayList<Sql> getSqllist() {
        return sqllist;
    }

    public String getSql() {
        return oldSql;
    }

    private void getFromList() throws Exception {
        fromlist.clear();
        int len=sqllist.size();
        if(len==0) return;
        Integer l1=sqllist.get(0).getFrom();
        Integer l2=sqllist.get(0).getWhere();
        Integer l3=-1;
        if(l2==null) l2=sqllist.get(0).getEnd();
        if(len==1) {
            String s;
            if(sqllist.get(0).getWhere()==null)
                s=oldSql.substring(sqllist.get(0).getFrom(), sqllist.get(0).getEnd());
            else
                s=oldSql.substring(sqllist.get(0).getFrom(), sqllist.get(0).getWhere());
            s=s.replace("FROM","").trim();
            fromlist.addAll(getForm(s));
        }
        for(int i=1; i<len; i++){  //1
            Sql s=sqllist.get(i);
            if(s.getSelect()>=l1 && s.getEnd()<=l2 && s.getSelect()>l3){
                String bStr="";
                if(l3==-1){
                    bStr=oldSql.substring(l1,s.getSelect());
                    bStr=bStr.replace("FROM","").trim();
                    int bpos=bStr.lastIndexOf(",");
                    if(bpos>=0) bStr=bStr.substring(0,bpos).trim();
                    if(!"(".equals(bStr) && bStr.indexOf("SELECT")<0) fromlist.addAll(getForm(bStr));
                }
                String str=oldSql.substring(s.getSelect(), s.getEnd());
                String alais=null;
                int pos=str.lastIndexOf(")");
                ArrayList<From> otherFrom=null;
                if(str.indexOf("SELECT")>=0){
                    if(pos>=0){
                        alais=str.substring(pos+1, str.length());
                        int apos=alais.indexOf(",");
                        if(apos>=0){
                            String oStr=alais.substring(apos+1, alais.length()).trim();
                            if(",".equals(rightStr(oStr,1))) oStr= oStr.substring(0, oStr.lastIndexOf(","));
                            alais=alais.substring(0, apos);
                            if(!"".equals(oStr)) otherFrom=getForm(oStr);
                        }
                        alais=alais.trim();
                        str=str.substring(0,pos);
                        fromlist.add(new From(str, alais));
                        if(otherFrom!=null) fromlist.addAll(otherFrom);
                    }
                }
                l3=s.getEnd();
            }
        }
        if(len>0 && fromlist.size()==0){
            Sql s=sqllist.get(0);
            String str="";
            if(s.getWhere()!=null)str=oldSql.substring(s.getFrom(), s.getWhere());
            else if(s.getOrder()!=null) str=oldSql.substring(s.getFrom(), s.getOrder());
            else if(s.getGroup()!=null) str=oldSql.substring(s.getFrom(), s.getGroup());
            else str=oldSql.substring(s.getFrom(), s.getEnd());
            str=str.trim();
            str=str.substring(5, str.length());
            fromlist=getForm(str);
        }
    }

    private void getAllList() throws Exception {
        int len=key.size();
        for(int i=0; i<len; i++){
            String k=key.get(i).getKey();
            Integer pos=key.get(i).getPos();
            if("SELECT".equals(k)){
                Sql s=new Sql();
                s.setSelect(pos);
                int j=setSqlDetail(i, len, s);
                sqllist.add(s);
                i=j;
            } else {
                int inx=0;
                if(i!=len-1) inx=getSqlIndex(k, pos, i);
                Sql s=sqllist.get(inx);
                i=setSqlDetail(i, len, s);
                sqllist.set(inx, s);
            }
        }
        System.out.println(JSON.toJSONString(sqllist));
        setSqllistEnd();
    }

    private void getList(String key){
        Integer pos=sql.indexOf(key);
        while(pos>=0){
            int inx=getKeyIndex(pos);
            if(inx<0) this.key.add(new Key(key, pos));
            else this.key.add(inx, new Key(key, pos));
            pos=sql.indexOf(key, pos+key.length());
        }
    }

    private int getKeyIndex(int pos){
        int inx=-1;
        int len=key.size();
        for(int i=0; i<len; i++){
            if(pos<=key.get(i).getPos()){
                inx=i;
                break;
            }
        }
        return inx;
    }

    private void setSqllistEnd() throws Exception {
        int len=sqllist.size();
        for(int i=0; i<len; i++){
            Sql s=sqllist.get(i);
            if(i==0) s.setEnd(oldSql.length());
            else{
                Integer l=s.getOrder();
                if(l==null) l=s.getGroup();
                if(l==null) l=s.getWhere();
                if(l==null) l=s.getFrom();
                if(l==null) throw new Exception("当前SELECT的语法有误,请检查!");
                Integer l1=getKeyIndex(l);
                if(i!=len-1){
                    int pos=key.get(l1).getPos();
                    String str=sql.substring(l,pos).trim();
                    if("(".equals(rightStr(str,1)) && "FROM".equals(leftStr(str,4))){
                        l1=getKeyIndex(pos);
                        pos=key.get(l1).getPos();
                        str=sql.substring(l,pos).trim();
                        String s1=rightStr(str,1);
                        while( !(")".equals(s1) ||
                                  ",".equals(s1) ||
                                  "(".equals(s1) ) && getKeyIndex(pos)>=0){
                            l1=getKeyIndex(pos);
                            pos=key.get(l1).getPos();
                            str=sql.substring(l,pos).trim();
                            s1=rightStr(str,1);
                        }
                    }
                }
                s.setEnd(key.get(l1).getPos()-1);
            }
        }
    }

    private int setDetail(int i, Sql s){
        if(i>=key.size()) return -1;
        int flag=0;
        String k=key.get(i).getKey();
        if("SELECT".equals(k)) flag=-1;
        switch (k){
            case "FROM":{
                if(s.getFrom()==null){
                    s.setFrom(key.get(i).getPos());
                    flag=1;
                }
                break;
            }
            case "WHERE":{
                if(s.getWhere()==null){
                    s.setWhere(key.get(i).getPos());
                    s.setEnd(true);
                    flag=1;
                }
                break;
            }
            case "GROUP":{
                if(s.getGroup()==null){
                    s.setGroup(key.get(i).getPos());
                    flag=1;
                }
                break;
            }
            case "ORDER":{
                if(s.getOrder()==null) {
                    s.setOrder(key.get(i).getPos());
                    flag=1;
                }
                break;
            }
        }
        return flag;
    }

    private int setSqlDetail(int i, int len, Sql s){
        int j=i;
        if(i==len-1){
            setDetail(i, s);
        } else {
            int flag=setDetail(i, s);
            if(flag==1) j=i;
            flag=setDetail(i+1, s);
            if(flag==1) j=i+1;
            if(flag>-1){
                flag=setDetail(i+2, s);
                if(flag==1) j=i+2;
                if(flag>-1){
                    flag=setDetail(i+3, s);
                    if(flag==1) j=i+3;
                    if(flag>-1){
                        flag=setDetail(i+4, s);
                        if(flag==1) j=i+4;
                    }
                }
            }
        }
        return j;
    }

    private Integer getSqlIndex(String k, Integer pos, int keyIndex) throws Exception {
        int len=sqllist.size();
        for(int i=len-1; i>=0; i--){
            Sql s=sqllist.get(i);
            if(!s.isEnd){
                int j=getWhereIndex(i, len, k, pos, keyIndex);
                if(i==j){
                    switch (k){
                        case "FROM":{
                            if(s.getFrom()==null) return i;
                            break;
                        }
                        case "WHERE":{
                            if(s.getWhere()==null) return i;
                            break;
                        }
                        case "GROUP":{
                            if(s.getGroup()==null) return i;
                            break;
                        }
                        case "ORDER":{
                            if(s.getOrder()==null) return i;
                            break;
                        }
                    }
                    if(s.getSelect().equals(pos)){
                        return i;
                    }
                }
                else return j;
            }
        }
        return -1;
    }

    private int getWhereIndex(int i, int len, String k, Integer pos, int keyIndex){
        if(!k.equals("WHERE")) return i;
        String str=getWhereIndexStr(i, len, k, pos);
        if(str!=null){
            while(str!=null && "(".equals(rightStr(str,1))){
                i--;
                str=getWhereIndexStr(i, len, k, pos);
                if(str!=null) sqllist.get(i).setEnd(true);
                else {
                    i++;
                    str=getWhereIndexStr(i, len, k, pos);
                    break;
                }
            }
            if(i==0) i++;
        }
        return i;
    }

    private String getWhereIndexStr(int i, int len, String k, Integer pos){
        String str=null;
        if(i==len || i==-1) return null;
        if(sqllist.get(i).isEnd()) return null;
        int st=sqllist.get(i).getLastPos();
        int en=-1;
        if(i+1<len) en=sqllist.get(i+1).getSelect();
        if(en>st) str=sql.substring(st, en);
        return str;
    }

}
